import 'dart:async';
import 'dart:io';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';
import 'package:quiver/time.dart';

class DataBases {
  String dbName = 'app.db';
  String view = 'view_table';
  String path;
  Database db;

  //播放记录 mid 视频id  title 视频名称 sutitle集数名称  ptime 播放到时间 time 点击时间
  String view_table_sql =
      'CREATE TABLE view_table (id INTEGER PRIMARY KEY,mid integer not null, title TEXT, sutitle TEXT,ptime Text,time Text)';

  Future<String> _createNewDb() async {
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    print(documentsDirectory);
    String path = join(documentsDirectory.path, dbName);
    if (await new Directory(dirname(path)).exists()) {
//      await deleteDatabase(path);
    } else {
      try {
        await new Directory(dirname(path)).create(recursive: true);
      } catch (e) {
        print("c错误:$e");
      }
    }
    return path;
  }

  Future query_table() async {
    List<Map> list = await db.rawQuery(
        "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;");
    return list;
  }

  open_table() async {
    path = await _createNewDb();
    db = await openDatabase(path);
    List tabLsit = await query_table();
    bool isCur = true;
    for (var i = 0; i < tabLsit.length; i++) {
      if (tabLsit[i]['name'].toString() == view.toString()) {
        print("数据库存在");
        isCur = false;
      }
    }
    if (isCur) {
      await db.execute(view_table_sql);
    }
  }

  Future<List<Map>> query_view_table_list({int curPage = 1}) async {
    int count = Sqflite
        .firstIntValue(await db.rawQuery("SELECT COUNT(*) FROM ${view}"));
    int pageSize = (count / 20).ceil();
    print("当前页数：${curPage}");
    print("总页数：${pageSize}");
    int start = (curPage - 1) * 20;
    print("数：${start}");
    List<Map> maps = await db.query(
      view,
      columns: ['id', 'mid', 'title', 'sutitle', 'ptime', 'time'],
      offset: start,
      limit: 20,
      orderBy: 'time desc',
    );
    await db.close();
    return maps;
  }

  Future<bool> del_table_list({int id = 0}) async {
    String sql = "";
    if (id == 0) {
      sql = 'DELETE FROM ${view}';
    } else {
      sql = 'DELETE FROM ${view} where id= ${id}';
    }
    int count = await db.rawDelete(sql);
    await db.close();
    return true;
  }

  //查询数据是否存在
  query_is_view_table(int mid) async {
    int count = Sqflite.firstIntValue(
        await db.rawQuery("SELECT COUNT(*) FROM ${view} where mid =${mid}"));
    return count;
  }

  update_insert_view_table(
      {int mid, String title, String sutitle, String ptime}) async {
    int countNum = await query_is_view_table(mid);
    if (countNum > 0) {
      int count = await db.rawUpdate(
          'UPDATE ${view} SET  title = ? , sutitle = ? , ptime = ?, time = ? WHERE mid = ?',
          [title, sutitle, ptime, DateTime.now().toString(), mid]);
      print("updated: $count");
    } else {
      int id2 = await db.rawInsert(
          'INSERT INTO ${view}(mid, title, sutitle,ptime,time) VALUES(?, ?, ?, ?, ?)',
          [mid, title, sutitle, ptime, DateTime.now().toString()]);
      print("inserted2: $id2");
    }
    await db.close();
  }
}
